--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_artent;

CREATE TABLE se_gam_artent AS (SELECT * FROM &erp.gam_artent WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES e  WHERE id LIKE '%201700%' /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d,UV_PENT_GAMMES e  WHERE e.id=d.nogamme and d.id LIKE '0%' ORDER BY id /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT DISTINCT DETGAM FROM UV_PPT_GAMMES_DETGAM ORDER BY 1/*gamme ligne article */



 -- select * from uv_pfamilles order by ID
 -- select * from se_ART_FAM
 -- select * from &erp.ART_FAM



 INSERT INTO se_gam_artent
 (SELECT SubStr(p.prodbase,1,Length(p.prodbase)-1) gene_cod,
         p.indice gene_ind,
         9099 unfab_cod,
         '0' artent_ind,
         NULL enreg_id,
         'N' enreg_s,
         To_Date('31/12/1967','dd/mm/yyyy')+datcre deb_dat,
         NULL fin_dat,
         p.des artent_lib,
         NULL fit_cod,
         'E' traitlig_typ
    FROM UV_PPT_GAMMES g,uv_pproduits p
    WHERE p.id=g.id) ;

 UPDATE se_gam_artent tf SET enreg_id=(select enreg_id FROM &erp.gam_artent f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind AND f.unfab_cod=tf.unfab_cod AND f.artent_ind=tf.artent_ind ) ;
 UPDATE se_gam_artent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('gene_cod@gene_ind@unfab_cod@artent_ind','GAM_ARTENT','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source, gene_cod, gene_ind, unfab_cod, artent_ind, enreg_id, enreg_s, deb_dat, fin_dat, artent_lib, fit_cod, traitlig_typ
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_artent g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_artent g
  )
GROUP BY gene_cod, gene_ind, unfab_cod, artent_ind, enreg_id, enreg_s, deb_dat, fin_dat, artent_lib, fit_cod, traitlig_typ
HAVING Count(1)!=2) r
ORDER BY gene_cod, gene_ind, unfab_cod ;